--    Program: 		CAPPOIMP_SUPPLIER.sql
--    Description:  Import PO Supplier information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_vendor_id				number;
   lv_segment				number;
   lv_segment1					number;                 		                 		
   
   cursor cur_vendors is
      select * from CRST_PO_VENDORS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   
   for cur in cur_vendors loop   	
     l_err_msg_tmp := '';
     
     select PO_VENDORS_S.NEXTVAL 
     into l_vendor_id
     FROM DUAL;
     
      select max(to_number(segment1))
      INTO lv_segment
      from po_vendors;
 
     lv_segment1 := lv_segment + 1;
     
     update CRST_PO_VENDORS
     set    vendor_id_n = l_vendor_id
     where  vendor_id = cur.VENDOR_ID;
     
     update PO_VENDORS
     set    vendor_name = CAPBRIMP_MAPPING.RenameStr(SUBSTR(vendor_name,1, 4), vendor_name)
     where  vendor_id = cur.VENDOR_ID;
          
     begin    
       -- import data
       insert into PO_VENDORS
       (
         VENDOR_ID                       ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATED_BY                 ,
         VENDOR_NAME                     ,
         VENDOR_NAME_ALT                 ,
         SEGMENT1                        ,
         SUMMARY_FLAG                    ,
         ENABLED_FLAG                    ,
         SEGMENT2                        ,
         SEGMENT3                        ,
         SEGMENT4                        ,
         SEGMENT5                        ,
         LAST_UPDATE_LOGIN               ,
         CREATION_DATE                   ,
         CREATED_BY                      ,
         EMPLOYEE_ID                     ,
         VENDOR_TYPE_LOOKUP_CODE         ,
         CUSTOMER_NUM                    ,
         ONE_TIME_FLAG                   ,
         PARENT_VENDOR_ID                ,
         MIN_ORDER_AMOUNT                ,
         SHIP_TO_LOCATION_ID             ,
         BILL_TO_LOCATION_ID             ,
         SHIP_VIA_LOOKUP_CODE            ,
         FREIGHT_TERMS_LOOKUP_CODE       ,
         FOB_LOOKUP_CODE                 ,
         TERMS_ID                        ,
         SET_OF_BOOKS_ID                 ,
         CREDIT_STATUS_LOOKUP_CODE       ,
         CREDIT_LIMIT                    ,
         ALWAYS_TAKE_DISC_FLAG           ,
         PAY_DATE_BASIS_LOOKUP_CODE      ,
         PAY_GROUP_LOOKUP_CODE           ,
         PAYMENT_PRIORITY                ,
         INVOICE_CURRENCY_CODE           ,
         PAYMENT_CURRENCY_CODE           ,
         INVOICE_AMOUNT_LIMIT            ,
         EXCHANGE_DATE_LOOKUP_CODE       ,
         HOLD_ALL_PAYMENTS_FLAG          ,
         HOLD_FUTURE_PAYMENTS_FLAG       ,
         HOLD_REASON                     ,
         DISTRIBUTION_SET_ID             ,
         ACCTS_PAY_CODE_COMBINATION_ID   ,
         DISC_LOST_CODE_COMBINATION_ID   ,
         DISC_TAKEN_CODE_COMBINATION_ID  ,
         EXPENSE_CODE_COMBINATION_ID     ,
         PREPAY_CODE_COMBINATION_ID      ,
         NUM_1099                        ,
         TYPE_1099                       ,
         WITHHOLDING_STATUS_LOOKUP_CODE  ,
         WITHHOLDING_START_DATE          ,
         ORGANIZATION_TYPE_LOOKUP_CODE   ,
         VAT_CODE                        ,
         START_DATE_ACTIVE               ,
         END_DATE_ACTIVE                 ,
         MINORITY_GROUP_LOOKUP_CODE      ,
         PAYMENT_METHOD_LOOKUP_CODE      ,
         BANK_ACCOUNT_NAME               ,
         BANK_ACCOUNT_NUM                ,
         BANK_NUM                        ,
         BANK_ACCOUNT_TYPE               ,
         WOMEN_OWNED_FLAG                ,
         SMALL_BUSINESS_FLAG             ,
         STANDARD_INDUSTRY_CLASS         ,
         HOLD_FLAG                       ,
         PURCHASING_HOLD_REASON          ,
         HOLD_BY                         ,
         HOLD_DATE                       ,
         TERMS_DATE_BASIS                ,
         PRICE_TOLERANCE                 ,
         INSPECTION_REQUIRED_FLAG        ,
         RECEIPT_REQUIRED_FLAG           ,
         QTY_RCV_TOLERANCE               ,
         QTY_RCV_EXCEPTION_CODE          ,
         ENFORCE_SHIP_TO_LOCATION_CODE   ,
         DAYS_EARLY_RECEIPT_ALLOWED      ,
         DAYS_LATE_RECEIPT_ALLOWED       ,
         RECEIPT_DAYS_EXCEPTION_CODE     ,
         RECEIVING_ROUTING_ID            ,
         ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
         ALLOW_UNORDERED_RECEIPTS_FLAG   ,
         HOLD_UNMATCHED_INVOICES_FLAG    ,
         EXCLUSIVE_PAYMENT_FLAG          ,
         AP_TAX_ROUNDING_RULE            ,
         AUTO_TAX_CALC_FLAG              ,
         AUTO_TAX_CALC_OVERRIDE          ,
         AMOUNT_INCLUDES_TAX_FLAG        ,
         TAX_VERIFICATION_DATE           ,
         NAME_CONTROL                    ,
         STATE_REPORTABLE_FLAG           ,
         FEDERAL_REPORTABLE_FLAG         ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         REQUEST_ID                      ,
         PROGRAM_APPLICATION_ID          ,
         PROGRAM_ID                      ,
         PROGRAM_UPDATE_DATE             ,
         OFFSET_VAT_CODE                 ,
         VAT_REGISTRATION_NUM            ,
         AUTO_CALCULATE_INTEREST_FLAG    ,
         VALIDATION_NUMBER               ,
         EXCLUDE_FREIGHT_FROM_DISCOUNT   ,
         TAX_REPORTING_NAME              ,
         CHECK_DIGITS                    ,
         BANK_NUMBER                     ,
         ALLOW_AWT_FLAG                  ,
         AWT_GROUP_ID                    ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         EDI_TRANSACTION_HANDLING        ,
         EDI_PAYMENT_METHOD              ,
         EDI_PAYMENT_FORMAT              ,
         EDI_REMITTANCE_METHOD           ,
         EDI_REMITTANCE_INSTRUCTION      ,
         BANK_CHARGE_BEARER              ,
         BANK_BRANCH_TYPE                ,
         MATCH_OPTION                    ,
         FUTURE_DATED_PAYMENT_CCID       ,
         CREATE_DEBIT_MEMO_FLAG          ,
         OFFSET_TAX_FLAG     
       )
       values
       (
         l_vendor_id                         ,
         cur.LAST_UPDATE_DATE                ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.VENDOR_NAME                     ,
         cur.VENDOR_NAME_ALT                 ,
         lv_segment1                       ,
         cur.SUMMARY_FLAG                    ,
         cur.ENABLED_FLAG                    ,
         cur.SEGMENT2                        ,
         cur.SEGMENT3                        ,
         cur.SEGMENT4                        ,
         cur.SEGMENT5                        ,
         cur.LAST_UPDATE_LOGIN               ,
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         CAPBRIMP_MAPPING.GetEmployeeMapping(cur.EMPLOYEE_ID),
         cur.VENDOR_TYPE_LOOKUP_CODE         ,
         cur.CUSTOMER_NUM                    ,
         cur.ONE_TIME_FLAG                   ,
         cur.PARENT_VENDOR_ID                ,
         cur.MIN_ORDER_AMOUNT                ,
         CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_TO_LOCATION_ID),
         CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.BILL_TO_LOCATION_ID),
         cur.SHIP_VIA_LOOKUP_CODE            ,
         cur.FREIGHT_TERMS_LOOKUP_CODE       ,
         cur.FOB_LOOKUP_CODE                 ,
         cur.TERMS_ID                        ,
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
         cur.CREDIT_STATUS_LOOKUP_CODE       ,
         cur.CREDIT_LIMIT                    ,
         cur.ALWAYS_TAKE_DISC_FLAG           ,
         cur.PAY_DATE_BASIS_LOOKUP_CODE      ,
         cur.PAY_GROUP_LOOKUP_CODE           ,
         cur.PAYMENT_PRIORITY                ,
         cur.INVOICE_CURRENCY_CODE           ,
         cur.PAYMENT_CURRENCY_CODE           ,
         cur.INVOICE_AMOUNT_LIMIT            ,
         cur.EXCHANGE_DATE_LOOKUP_CODE       ,
         cur.HOLD_ALL_PAYMENTS_FLAG          ,
         cur.HOLD_FUTURE_PAYMENTS_FLAG       ,
         cur.HOLD_REASON                     ,
         cur.DISTRIBUTION_SET_ID             ,
         cur.ACCTS_PAY_CODE_COMBINATION_ID   ,
         cur.DISC_LOST_CODE_COMBINATION_ID   ,
         cur.DISC_TAKEN_CODE_COMBINATION_ID  ,
         cur.EXPENSE_CODE_COMBINATION_ID     ,
         cur.PREPAY_CODE_COMBINATION_ID      ,
         cur.NUM_1099                        ,
         cur.TYPE_1099                       ,
         cur.WITHHOLDING_STATUS_LOOKUP_CODE  ,
         cur.WITHHOLDING_START_DATE          ,
         cur.ORGANIZATION_TYPE_LOOKUP_CODE   ,
         cur.VAT_CODE                        ,
         cur.START_DATE_ACTIVE               ,
         cur.END_DATE_ACTIVE                 ,
         cur.MINORITY_GROUP_LOOKUP_CODE      ,
         cur.PAYMENT_METHOD_LOOKUP_CODE      ,
         cur.BANK_ACCOUNT_NAME               ,
         cur.BANK_ACCOUNT_NUM                ,
         cur.BANK_NUM                        ,
         cur.BANK_ACCOUNT_TYPE               ,
         cur.WOMEN_OWNED_FLAG                ,
         cur.SMALL_BUSINESS_FLAG             ,
         cur.STANDARD_INDUSTRY_CLASS         ,
         cur.HOLD_FLAG                       ,
         cur.PURCHASING_HOLD_REASON          ,
         cur.HOLD_BY                         ,
         cur.HOLD_DATE                       ,
         cur.TERMS_DATE_BASIS                ,
         cur.PRICE_TOLERANCE                 ,
         cur.INSPECTION_REQUIRED_FLAG        ,
         cur.RECEIPT_REQUIRED_FLAG           ,
         cur.QTY_RCV_TOLERANCE               ,
         cur.QTY_RCV_EXCEPTION_CODE          ,
         cur.ENFORCE_SHIP_TO_LOCATION_CODE   ,
         cur.DAYS_EARLY_RECEIPT_ALLOWED      ,
         cur.DAYS_LATE_RECEIPT_ALLOWED       ,
         cur.RECEIPT_DAYS_EXCEPTION_CODE     ,
         cur.RECEIVING_ROUTING_ID            ,
         cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
         cur.ALLOW_UNORDERED_RECEIPTS_FLAG   ,
         cur.HOLD_UNMATCHED_INVOICES_FLAG    ,
         cur.EXCLUSIVE_PAYMENT_FLAG          ,
         cur.AP_TAX_ROUNDING_RULE            ,
         cur.AUTO_TAX_CALC_FLAG              ,
         cur.AUTO_TAX_CALC_OVERRIDE          ,
         cur.AMOUNT_INCLUDES_TAX_FLAG        ,
         cur.TAX_VERIFICATION_DATE           ,
         cur.NAME_CONTROL                    ,
         cur.STATE_REPORTABLE_FLAG           ,
         cur.FEDERAL_REPORTABLE_FLAG         ,
         cur.ATTRIBUTE_CATEGORY              ,
         cur.ATTRIBUTE1                      ,
         cur.ATTRIBUTE2                      ,
         cur.ATTRIBUTE3                      ,
         cur.ATTRIBUTE4                      ,
         cur.ATTRIBUTE5                      ,
         cur.ATTRIBUTE6                      ,
         cur.ATTRIBUTE7                      ,
         cur.ATTRIBUTE8                      ,
         cur.ATTRIBUTE9                      ,
         cur.ATTRIBUTE10                     ,
         cur.ATTRIBUTE11                     ,
         cur.ATTRIBUTE12                     ,
         cur.ATTRIBUTE13                     ,
         cur.ATTRIBUTE14                     ,
         cur.ATTRIBUTE15                     ,
         cur.REQUEST_ID                      ,
         cur.PROGRAM_APPLICATION_ID          ,
         cur.PROGRAM_ID                      ,
         cur.PROGRAM_UPDATE_DATE             ,
         cur.OFFSET_VAT_CODE                 ,
         cur.VAT_REGISTRATION_NUM            ,
         cur.AUTO_CALCULATE_INTEREST_FLAG    ,
         cur.VALIDATION_NUMBER               ,
         cur.EXCLUDE_FREIGHT_FROM_DISCOUNT   ,
         cur.TAX_REPORTING_NAME              ,
         cur.CHECK_DIGITS                    ,
         cur.BANK_NUMBER                     ,
         cur.ALLOW_AWT_FLAG                  ,
         cur.AWT_GROUP_ID                    ,
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.EDI_TRANSACTION_HANDLING        ,
         cur.EDI_PAYMENT_METHOD              ,
         cur.EDI_PAYMENT_FORMAT              ,
         cur.EDI_REMITTANCE_METHOD           ,
         cur.EDI_REMITTANCE_INSTRUCTION      ,
         cur.BANK_CHARGE_BEARER              ,
         cur.BANK_BRANCH_TYPE                ,
         cur.MATCH_OPTION                    ,
         cur.FUTURE_DATED_PAYMENT_CCID       ,
         cur.CREATE_DEBIT_MEMO_FLAG          ,
         cur.OFFSET_TAX_FLAG                 
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   	 
   	 update cpo_vendors v1
   	 set    s_org_id = (select CAPBRIMP_MAPPING.GetOrgMapping(v2.s_org_id)
	                    from   CRST_PO_VENDORS cv2, cpo_vendors v2
						where  cv2.vendor_id_n = v1.vendor_id
						and    cv2.vendor_id = v2.vendor_id),
   	        s_business_group_id = (select CAPBRIMP_MAPPING.GetBGMapping(v2.s_business_group_id)
	                               from   CRST_PO_VENDORS cv2, cpo_vendors v2
						           where  cv2.vendor_id_n = v1.vendor_id
						           and    cv2.vendor_id = v2.vendor_id)
   	 where  exists (select 'X'
   	                from   CRST_PO_VENDORS cv1
   	                where  cv1.vendor_id_n = v1.vendor_id);
   	                
   	 commit;               
   	        
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_SUPPLIER', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/
